package com.xxl.job.executor.service.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IDeptmentMonitorService;

@Service("DeptmentMonitorService")
public class DeptmentMonitorService implements IDeptmentMonitorService {

    @Override
    public void deptmentMonitor() throws Exception {
        XxlJobLogger.log("=====开始执行业务操作=====");
        DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSourcePre);
        Connection conn = null;
        final String recordName = "TB_SYNC_DATAMONITOR";
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            conn.setAutoCommit(false);           
            String recordStr = DBUtils.getCurDate(conn);
           
            //记录表同步时间为同步开始时间
            String syncEndDate = recordStr + " 00:00:00";  
            //获取记录表后第七天时间为同步时间
           //ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
           //String dataDays = configProperties.getDataDays();         
          //  String syncEndDate = getFetureDate(7,recordStr);
           String syncStartDate = DBUtils.getServenDayAgoData(conn,syncEndDate);           
           syncEndDate =  recordStr + " 23:59:59";

           // XxlJobLogger.log("=====开始同步异常数据统计=====");
            //同步表的数据
            insertExceptionStation(syncStartDate,syncEndDate,conn);
            //统计异常表的总数
            udpateExceptionTotalcnt(syncStartDate,syncEndDate,conn);
            //省接收门架流水数统计
            updateGrantryReceiveNum(syncStartDate,syncEndDate,conn) ;            
            // 统计etc入口的省接受流水数
            updateEtcInReceiveNum(syncStartDate,syncEndDate,conn);         
             // 统计mtc入口的省接受流水数
            updateMtcInReceiveNum(syncStartDate,syncEndDate,conn);      
            //统计etc出口省接收流水数
            updateETCBoutlistReceiveNum(syncStartDate,syncEndDate,conn);           
             // 统计mtc出口省接收流水数       
           updateBoutlistReceiveNum(syncStartDate,syncEndDate,conn);
            //删除30天前的旧数据            
            deleteOldData(conn);
            
            conn.commit();
            XxlJobLogger.log("=====完成执行业务操作===");
        }catch(Exception e){
            if (conn != null) {
                try {
                    conn.rollback();
                    XxlJobLogger.log("回滚成功.");
                }catch (Exception e1){
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("抛出异常："+e.getMessage());
            throw e;
        }finally{
            if (conn != null) {
                try {
                    conn.setAutoCommit(true);
                    conn.close();
                }catch (Exception e){
                    e.printStackTrace();
                }
            }
        }
    }

    public void insertExceptionStation(String startTime,String endTime,Connection conn) throws Exception{
    	
    	  //同步或更新TB_SYNC_DEPTMONITOR_EXCEPTION表 站-部数据传输异常明细表
        String deptmonitorException = "  MERGE INTO TB_SYNC_DEPTMONITOR_EXCEPTION A  \r\n" + 
        		"    USING (  \r\n" + 
        		"      select  ts.ID,ts.TRADEDATE,ts.TRADEHOUR,ts.TRADETYPE,gss.road_id,  \r\n" + 
        		"      ts.STATION_ID, ts.LANENO,ts.INSERTTIME,ts.RESPONSECODE,ts.RESPONSEMESSAGE,ts.IPADDRESS,tS.INSERTTABLETIME    \r\n" + 
        		"   from  ( select t.id,t.TRADEDATE,t.TRADEHOUR,t.TRADETYPE,gs.station_id,t.LANENO,t.INSERTTIME,  \r\n" + 
        		"    t.RESPONSECODE,t.RESPONSEMESSAGE,t.IPADDRESS,t.INSERTTABLETIME   \r\n" + 
        		"    from MONITORDEPT_EXCEPTION t\r\n" + 
        		"    inner  join tb_charge_grantry_station gs on t.stationid=gs.grantry_id                      \r\n" + 
        		"    where t.tradetype='tbl_GBUPLOAD_ETCTU_send' \r\n" + 
        		"    union all\r\n" + 
        		"    select t.id,t.TRADEDATE,t.TRADEHOUR,t.TRADETYPE,s.station_id,t.LANENO,t.INSERTTIME,  \r\n" + 
        		"    t.RESPONSECODE,t.RESPONSEMESSAGE,t.IPADDRESS,t.INSERTTABLETIME   \r\n" + 
        		"    from MONITORDEPT_EXCEPTION t\r\n" + 
        		"    LEFT JOIN TB_CHARGE_gbSTATION S on  t.STATIONID = S.STATION_gbid                  \r\n" + 
        		"    where t.tradetype<>'tbl_GBUPLOAD_ETCTU_send' \r\n" + 
        		"    ) ts               \r\n" + 
        		"   left join TB_CHARGE_STATION gss on ts.station_id=gss.station_id   \r\n" + 
        		"   WHERE  ts.STATION_ID is not null \r\n" + 
        		"    and INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')    \r\n" + 
        		"    AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')           \r\n" + 
        		"   ) B \r\n" + 
        		"   ON ( A.ID = B.ID and A.INSERT_TIME=b.INSERTTIME AND A.TRADE_DATE = B.TRADEDATE AND A.TRADE_TYPE = B.TRADETYPE AND A.STATION_ID = B.STATION_ID )   \r\n" + 
        		"   WHEN NOT MATCHED THEN   \r\n" + 
        		"   INSERT  (A.ID,A.TRADE_DATE,A.TRADE_HOUR,A.TRADE_TYPE,A.ROAD_ID,A.STATION_ID,A.LANE_NO,A.INSERT_TIME,  \r\n" + 
        		"   A.RESPONSE_CODE,A.RESPONSE_MESSAGE,A.IP_ADDRESS,a.update_time)   \r\n" + 
        		"   VALUES  (B.ID,B.TRADEDATE,B.TRADEHOUR,B.TRADETYPE,B.ROAD_ID,B.STATION_ID,B.LANENO,B.INSERTTIME,  \r\n" + 
        		"   B.RESPONSECODE,B.RESPONSEMESSAGE,B.IPADDRESS,SYSDATE)  ";       
        Integer  count = DBUtils.execute(conn, deptmonitorException);
		XxlJobLogger.log("[普通]本次统计站-部异常数据传输完整性监视表数据(条):" + count);	
    	
        //同步或更新TB_SYNC_DEPTMENT_MONITOR表 站-部数据传输完整性监视表
        String deptmentMonitor = " MERGE INTO TB_SYNC_DEPTMENT_MONITOR A    " + 
        		"      using (  " + 
        		"      select * from (" + 
        		"            select TRADEDATE, TRADETYPE," + 
        		"             case when s.station_id is null then gs.STATION_ID  else s.station_id end as STATION_ID,   " + 
        		"              case when s.road_id is null then gss.road_id else s.road_id end ROAD_ID,t.stationtotalnum,t.stationstocknum,    " + 
        		"                 t.syncnum,t.sendnum,NOTSENDNUM,UPDATETIME,INSERTTABLETIME     " + 
        		"            from MONITOR_DEPTMENT t " + 
        		"            LEFT JOIN TB_CHARGE_gbSTATION S on  t.STATIONID = S.STATION_gbid     " + 
        		"            left join tb_charge_grantry_station gs on t.stationid=gs.grantry_id    " + 
        		"            left join TB_CHARGE_STATION gss on gs.station_id=gss.station_id   " + 
        		"            where INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  " +
        	    "               AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')   " +
        		 "              and (to_char(t.TRADEDATE,'yyyy-mm-dd'),t.TRADETYPE,t.STATIONID,t.UPDATETIME) in (" + 
        		"              SELECT  to_char(s.TRADEDATE,'yyyy-mm-dd'),s.TRADETYPE,s.STATIONID,max(s.UPDATETIME) " + 
        		"              FROM MONITOR_DEPTMENT s " + 
        		"              where  INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  "+
        		"                AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')   "+
        		"              GROUP BY to_char(s.TRADEDATE,'yyyy-mm-dd'),s.TRADETYPE,s.STATIONID" + 
        		"            ) " + 
        		"         ) where STATION_ID is not null        " + 
        		"           ) B     " + 
        		"         ON (A.TRADE_DATE = B.TRADEDATE AND A.TRADE_TYPE = B.TRADETYPE AND A.STATION_ID = B.STATION_ID)   " + 
        		"          WHEN MATCHED THEN   " + 
        		"              UPDATE SET  A.STATION_TOTALNUM = B.STATIONTOTALNUM,A.STATION_STOCK_NUM = B.STATIONSTOCKNUM,A.SYNC_NUM = B.SYNCNUM,  " + 
        		"                 A.SEND_NUM = B.SENDNUM,A.NOT_SEND_NUM = B.NOTSENDNUM,A.UPDATE_TIME = SYSDATE,A.PROVINCE_TIME = B.UPDATETIME  " + 
        		"           WHEN NOT MATCHED THEN    " + 
        		"              INSERT  (A.TRADE_DATE,A.TRADE_TYPE,A.STATION_ID,A.ROAD_ID,A.STATION_TOTALNUM,A.STATION_STOCK_NUM,     " + 
        		"               A.SYNC_NUM,A.SEND_NUM,A.NOT_SEND_NUM,A.INSERT_TIME,A.PROVINCE_TIME,UPDATE_TIME,EXCEPTION_TOTALCNT)     " + 
        		"             VALUES (B.TRADEDATE,B.TRADETYPE,B.STATION_ID,B.ROAD_ID,B.STATIONTOTALNUM,B.STATIONSTOCKNUM,    " + 
        		"                    B.SYNCNUM,B.SENDNUM,B.NOTSENDNUM,SYSDATE,B.UPDATETIME,SYSDATE,0) ";
    	 count = DBUtils.execute(conn, deptmentMonitor);
		XxlJobLogger.log("[普通]本次统计站-部数据传输完整性监视表数据(条):" + count);	

      


        //同步或更新TB_SYNC_PROVINCE_MONITOR表 站-省数据传输完整性监视表         
       String provinceMonitor=" MERGE INTO TB_SYNC_PROVINCE_MONITOR A \r\n" + 
       		"              USING (   \r\n" + 
       		"              select * from (  \r\n" + 
       		"                select t.TRADEDATE,t.TRADETYPE,case when s.station_id is null then gss.station_id else s.station_id end as station_id,\r\n" + 
       		"                        case when s.road_id is null then gss.road_id else s.road_id end road_id,\r\n" + 
       		"                        STATIONTOTALNUM,STATIONSTOCKNUM,\r\n" + 
       		"                         SYNCNUM,SENDNUM,NOTSENDNUM,UPDATETIME,INSERTTABLETIME\r\n" + 
       		"                 FROM MONITOR_PROVINCE t\r\n" + 
       		"                 LEFT JOIN TB_CHARGE_gbSTATION S on  t.STATIONID = S.STATION_gbid \r\n" + 
       		"                 left join tb_charge_grantry_station gs on t.stationid=gs.grantry_id\r\n" + 
       		"                 left join TB_CHARGE_STATION gss on gs.station_id=gss.station_id \r\n" + 
       		"                 where INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " +
       		"                 and  INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')  "+
       		"                 and  (to_char(t.TRADEDATE,'yyyy-mm-dd'),t.TRADETYPE,t.STATIONID,t.UPDATETIME) in (\r\n" + 
       		"                      SELECT  to_char(s.TRADEDATE,'yyyy-mm-dd'),s.TRADETYPE,s.STATIONID,max(s.UPDATETIME)  \r\n" + 
       		"        	            FROM MONITOR_PROVINCE s \r\n" + 
       		"                       where  s.INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')                  "+
       		"                       and  s.INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')                                                                                             "+
       		"        	            GROUP BY to_char(s.TRADEDATE,'yyyy-mm-dd'),s.TRADETYPE,s.STATIONID    \r\n" + 
       		"                  )\r\n" + 
       		"                ) where   station_id is not null\r\n" + 
       		"              ) B \r\n" + 
       		"              ON (A.TRADE_DATE = B.TRADEDATE AND A.TRADE_TYPE = B.TRADETYPE AND A.STATION_ID = B.STATION_ID) \r\n" + 
       		"              WHEN MATCHED THEN \r\n" + 
       		"              UPDATE SET \r\n" + 
       		"                      A.STATION_TOTALNUM = B.STATIONTOTALNUM,A.STATION_STOCK_NUM = B.STATIONSTOCKNUM,A.SYNC_NUM = B.SYNCNUM,\r\n" + 
       		"                      A.SEND_NUM = B.SENDNUM,A.NOT_SEND_NUM = B.NOTSENDNUM,A.UPDATE_TIME = SYSDATE,\r\n" + 
       		"                      A.PROVINCE_TIME = B.UPDATETIME \r\n" + 
       		"               WHEN NOT MATCHED THEN \r\n" + 
       		"               INSERT \r\n" + 
       		"                     (A.TRADE_DATE,A.TRADE_TYPE,A.STATION_ID,A.ROAD_ID,A.STATION_TOTALNUM,A.STATION_STOCK_NUM,A.SYNC_NUM,\r\n" + 
       		"                     A.SEND_NUM,A.NOT_SEND_NUM,A.INSERT_TIME,A.PROVINCE_TIME,RECEIVE_NUM)  \r\n" + 
       		"              VALUES \r\n" + 
       		"                     (B.TRADEDATE,B.TRADETYPE,B.STATION_ID,B.ROAD_ID,B.STATIONTOTALNUM,B.STATIONSTOCKNUM,B.SYNCNUM,\r\n" + 
       		"                    B.SENDNUM,B.NOTSENDNUM,SYSDATE,B.UPDATETIME,0)  ";
       		 count = DBUtils.execute(conn, provinceMonitor);
	         XxlJobLogger.log("[普通]本次统计站-省数据传输完整性监视表 数据(条):" + count);	
    }
    
    
    /**
     * 从异常明细表统计异常数套 异常表
     * @param startTime
     * @param endTime
     * @param conn
     */
    public void udpateExceptionTotalcnt(String startTime,String endTime,Connection conn)throws SQLException {
    	String sql = " merge into TB_SYNC_DEPTMENT_MONITOR a\r\n" + 
    			"using ( \r\n" + 
    			"     select to_date(TRADEDATE,'yyyy-mm-dd') TRADEDATE,tradetype,STATION_ID,count(1) as EXCEPTION_TOTALCNT from (\r\n" + 
    			"     select to_char(t.TRADEDATE,'yyyy-mm-dd') as TRADEDATE,t.tradetype,\r\n" + 
    			"     case when s.station_id is null then gs.STATION_ID  else s.station_id end as STATION_ID\r\n" + 
    			"     from MONITORDEPT_EXCEPTION t \r\n" + 
    			"     LEFT JOIN TB_CHARGE_gbSTATION S on  t.STATIONID = S.STATION_gbid     \r\n" + 
    			"     left join tb_charge_grantry_station gs on t.stationid=gs.grantry_id    \r\n" + 
    			"     left join TB_CHARGE_STATION gss on gs.station_id=gss.station_id   \r\n" +
    			"    WHERE INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') "+
    			"   AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')   " +     		
    			"    ) tp where tp.STATION_ID is not null\r\n" + 
    			"    group by TRADEDATE,tradetype,STATION_ID\r\n" + 
    			"   ) b\r\n" + 
    			" on (a.trade_date=b.TRADEDATE and a.TRADE_TYPE=b.tradetype and a.STATION_ID=b.STATION_ID)    \r\n" + 
    			" when matched then \r\n" + 
    			"     update set a.EXCEPTION_TOTALCNT = b.EXCEPTION_TOTALCNT,UPDATE_TIME=sysdate\r\n";
    			///" when not matched then \r\n" + 
    			//"     insert (a.TRADE_DATE,TRADE_TYPE,STATION_ID,EXCEPTION_TOTALCNT,INSERT_TIME,UPDATE_TIME)\r\n" + 
    			//"     values (b.TRADEDATE,b.tradetype,b.STATION_ID,b.EXCEPTION_TOTALCNT,sysdate,sysdate) ";
    	Integer count = DBUtils.execute(conn, sql);
         XxlJobLogger.log("[普通]本次统计站-部异常数据总数(条):" + count);	
    }

    /**
     * 省接收门架流水数统计
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    public void updateGrantryReceiveNum(String startTime,String endTime,Connection conn) throws SQLException{
             String sql = "  MERGE INTO TB_SYNC_PROVINCE_MONITOR A " + 
             		"   using (" + 
             		"     select to_date(substr(t.transtime,0,10),'yyyy-mm-dd') AS TRADE_DATE,'tbl_grantry_trans_main_sendPro' AS TRADE_TYPE,gs.station_id AS STATION_ID," + 
             		"     s.road_id AS ROAD_ID,count(1) AS RECEIVE_NUM " + 
             		"     from grantry_trans_main_all t " + 
             		"     inner join tb_charge_grantry_station gs on t.gantryid=gs.grantry_id" + 
             		"     inner join tb_charge_station s on gs.station_id=s.station_id" + 
             		"     WHERE t.transtime is not null " +
             	    "      and  INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " + 
             		"      AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')    " + 
             		"      group by to_date(substr(t.transtime,0,10),'yyyy-mm-dd'),gs.station_id,s.road_id        " + 
             		"    ) B  " + 
             		"   ON (A.TRADE_DATE=B.TRADE_DATE AND A.TRADE_TYPE=B.TRADE_TYPE AND A.STATION_ID=B.STATION_ID AND A.ROAD_ID=B.ROAD_ID)   " + 
             		"   WHEN MATCHED THEN " + 
             		"     update set a.RECEIVE_NUM=b.RECEIVE_NUM,a.UPDATE_TIME = sysdate";
             		//"   WHEN NOT MATCHED THEN" + 
             		//"     INSERT ( TRADE_DATE,TRADE_TYPE,STATION_ID,ROAD_ID,RECEIVE_NUM,UPDATE_TIME,INSERT_TIME )" + 
             		//"     VALUES (B.TRADE_DATE,B.TRADE_TYPE,B.STATION_ID,B.ROAD_ID,B.RECEIVE_NUM,SYSDATE,SYSDATE)";
         	Integer count = DBUtils.execute(conn, sql);
			XxlJobLogger.log("[普通]本次统计门架接受流水数据(条):" + count);	
    }
    
    
    /**
          * 统计所有etc入口的省接受流水数
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException 
     */
    public void updateEtcInReceiveNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql ="  MERGE INTO TB_SYNC_PROVINCE_MONITOR A \r\n" + 
    			"  using ( \r\n" + 
    			"     select to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd') as TRADE_DATE,'tbl_etcBInList_sendPro' as TRADE_TYPE, \r\n" + 
    			"      et.stationno as STATION_ID,s.road_id,count(1) as RECEIVE_NUM  \r\n" + 
    			"      from etcbinlist_all et \r\n" + 
    			"      inner join tb_charge_station s on et.stationno=s.station_id \r\n" + 
    			"        WHERE et.optime is not null  \r\n" + 
    			"     and  INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" + 
    			"      AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')      \r\n" + 
    			"      group by to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd'),et.stationno,s.road_id  \r\n" + 
    			"   ) B   \r\n" + 
    			"   ON (A.TRADE_DATE=B.TRADE_DATE AND A.TRADE_TYPE=B.TRADE_TYPE AND A.STATION_ID=B.STATION_ID AND A.ROAD_ID=B.ROAD_ID)    \r\n" + 
    			"   WHEN MATCHED THEN  \r\n" + 
    			"     update set a.RECEIVE_NUM=b.RECEIVE_NUM,a.UPDATE_TIME = sysdate  \r\n" ;
    			//"   WHEN NOT MATCHED THEN \r\n" + 
    			//"     INSERT ( TRADE_DATE,TRADE_TYPE,STATION_ID,ROAD_ID,RECEIVE_NUM,UPDATE_TIME,INSERT_TIME )  \r\n" + 
    			//"     VALUES (B.TRADE_DATE,B.TRADE_TYPE,B.STATION_ID,B.ROAD_ID,B.RECEIVE_NUM,SYSDATE,SYSDATE)  ";
    	
    	Integer count = DBUtils.execute(conn, sql);
		XxlJobLogger.log("[普通]本次统计etc入口接受流水数据(条):" + count);	
    }
    
    public void updateMtcInReceiveNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "  MERGE INTO TB_SYNC_PROVINCE_MONITOR A \r\n" + 
    			"  using ( \r\n" + 
    			"     select to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd') as TRADE_DATE,'tbl_BInList_sendPro' as TRADE_TYPE, \r\n" + 
    			"      et.stationno as STATION_ID,s.road_id,count(1) as RECEIVE_NUM  \r\n" + 
    			"      from binlist_all et \r\n" + 
    			"      inner join tb_charge_station s on et.stationno=s.station_id \r\n" + 
    			"        WHERE et.optime is not null  \r\n" + 
    			"     and  INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')  \r\n" + 
    			"     AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')       \r\n" + 
    			"      group by to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd'),et.stationno,s.road_id  \r\n" + 
    			"   ) B   \r\n" + 
    			"   ON (A.TRADE_DATE=B.TRADE_DATE AND A.TRADE_TYPE=B.TRADE_TYPE AND A.STATION_ID=B.STATION_ID AND A.ROAD_ID=B.ROAD_ID)    \r\n" + 
    			"   WHEN MATCHED THEN  \r\n" + 
    			"     update set a.RECEIVE_NUM=b.RECEIVE_NUM,a.UPDATE_TIME = sysdate   ";
    	Integer count = DBUtils.execute(conn, sql);
		XxlJobLogger.log("[普通]本次统计mtc入口接受流水数据(条):" + count);	
    }
    
    /**
          * 统计etc出口省接收流水数
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    public void updateETCBoutlistReceiveNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = "MERGE INTO TB_SYNC_PROVINCE_MONITOR A " + 
    			" using (" + 
    			"     select to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd') as TRADE_DATE,'tbl_etcBoutList_sendPro' as TRADE_TYPE," + 
    			"      et.stationno as STATION_ID,s.road_id,count(1) as RECEIVE_NUM " + 
    			"      from etcboutlist_all et " + 
    			"      inner join tb_charge_station s on et.stationno=s.station_id" + 
    			"        WHERE et.optime is not null " + 
    			"      and INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss')   " + 
    			"      AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')     " + 
    			"      group by to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd'),et.stationno,s.road_id     " + 
    			" ) B  " + 
    			" ON (A.TRADE_DATE=B.TRADE_DATE AND A.TRADE_TYPE=B.TRADE_TYPE AND A.STATION_ID=B.STATION_ID AND A.ROAD_ID=B.ROAD_ID)   " + 
    			" WHEN MATCHED THEN " + 
    			"     update set a.RECEIVE_NUM=b.RECEIVE_NUM,a.UPDATE_TIME = sysdate "; 
    			//" WHEN NOT MATCHED THEN " + 
    			//"     INSERT ( TRADE_DATE,TRADE_TYPE,STATION_ID,ROAD_ID,RECEIVE_NUM,UPDATE_TIME,INSERT_TIME ) " + 
    			//"     VALUES (B.TRADE_DATE,B.TRADE_TYPE,B.STATION_ID,B.ROAD_ID,B.RECEIVE_NUM,SYSDATE,SYSDATE)";
          	Integer count = DBUtils.execute(conn, sql);
    		XxlJobLogger.log("[普通]本次统计etc出口接受流水数据(条):" + count);	    	
    }
    
    /**
          * 统计mtc出口省接收流水数
     * @param startTime
     * @param endTime
     * @param conn
     * @throws SQLException
     */
    public void updateBoutlistReceiveNum(String startTime,String endTime,Connection conn) throws SQLException {
    	String sql = " MERGE INTO TB_SYNC_PROVINCE_MONITOR A " + 
    			" using (" + 
    			"     select to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd') as TRADE_DATE,'tbl_BOutList_sendPro' as TRADE_TYPE," + 
    			"      et.stationno as STATION_ID,s.road_id,count(1) as RECEIVE_NUM" + 
    			"      from boutlist_all et " + 
    			"      inner join tb_charge_station s on et.stationno=s.station_id" + 
    			"        WHERE et.optime is not null" + 
    			"      and INSERTTABLETIME >= TO_DATE('"+startTime+"','yyyy-MM-dd HH24:mi:ss') " + 
    			"     AND INSERTTABLETIME <= TO_DATE('"+endTime+"','yyyy-MM-dd HH24:mi:ss')     " + 
    			"      group by to_date(to_char(et.optime,'yyyy-mm-dd'),'yyyy-mm-dd'),et.stationno,s.road_id     " + 
    			" ) B  " + 
    			" ON (A.TRADE_DATE=B.TRADE_DATE AND A.TRADE_TYPE=B.TRADE_TYPE AND A.STATION_ID=B.STATION_ID AND A.ROAD_ID=B.ROAD_ID)  " + 
    			" WHEN MATCHED THEN " + 
    			"     update set a.RECEIVE_NUM=b.RECEIVE_NUM,a.UPDATE_TIME = sysdate" ;
    			//" WHEN NOT MATCHED THEN" + 
    			//"     INSERT ( TRADE_DATE,TRADE_TYPE,STATION_ID,ROAD_ID,RECEIVE_NUM,UPDATE_TIME,INSERT_TIME )" + 
    			//"     VALUES (B.TRADE_DATE,B.TRADE_TYPE,B.STATION_ID,B.ROAD_ID,B.RECEIVE_NUM,SYSDATE,SYSDATE) ";
      	Integer count = DBUtils.execute(conn, sql);
		XxlJobLogger.log("[普通]本次统计etc出口接受流水数据(条):" + count);	    
    }
    
    /**
     * 删除几天前的旧数据
     * @param connPre
     * @throws Exception
     */
    void deleteOldData(Connection connPre) throws Exception {
    	  //清除7天前的数据（配置文件中配置）
    	 String dbCurTime = DBUtils.getDBCurTime(connPre);
        String strHour = dbCurTime.substring(11,13);
        if (strHour.equals("02")) { //凌晨2点执行一次删除日前的数据
        	 String strCurDay = dbCurTime.substring(0,10);
            
             String thirtyDayAgo = DBUtils.getThirtyDayAgoData(connPre);           
                    
             // ConfigProperties configProperties = SpringContextHolder.getBean("configProperties");
             // String dataDays = configProperties.getDataDays(); 
             // String sqlPro = "delete from TB_SYNC_MONITOR_ETCTU_"+num+" where TRADE_DATE<to_char(sysdate-interval'"+dataDays+"'day, 'yyyy-mm-dd') ";
            String sqlDeleteDeptMent = " delete from TB_SYNC_DEPTMENT_MONITOR where TRADE_DATE<to_date('"+thirtyDayAgo+"','yyyy-mm-dd') ";	
            Integer delNum = DBUtils.execute(connPre, sqlDeleteDeptMent);
            XxlJobLogger.log("[普通]本次删除TB_SYNC_DEPTMENT_MONITOR表"+thirtyDayAgo+"天前数据(条):"+delNum);
            
            String sqlDeptException = " delete from TB_SYNC_DEPTMONITOR_EXCEPTION where  TRADE_DATE <to_date('"+thirtyDayAgo+"','yyyy-mm-dd') ";
             delNum = DBUtils.execute(connPre, sqlDeptException);
            XxlJobLogger.log("[普通]本次删除TB_SYNC_DEPTMONITOR_EXCEPTION表"+thirtyDayAgo+"天前数据(条):"+delNum);
            
            String sqlPROVINCE = " delete from TB_SYNC_PROVINCE_MONITOR where TRADE_DATE< to_date('\"+thirtyDayAgo+\"','yyyy-mm-dd') ";
            delNum = DBUtils.execute(connPre, sqlPROVINCE);
            XxlJobLogger.log("[普通]本次删除TB_SYNC_PROVINCE_MONITOR表"+thirtyDayAgo+"天前数据(条):"+delNum);
            
        }
    }

    /**
     * 获取未来 第 past 天的日期
     * @param past
     * @return
     */
    public static String getFetureDate(int past,String recordStr) throws Exception{
        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        calendar.setTime(format.parse(recordStr));
        calendar.set(Calendar.DAY_OF_YEAR, calendar.get(Calendar.DAY_OF_YEAR) + past - 1);
        Date today = calendar.getTime();
        String result = format.format(today);
        return result;
    }

    /**
     * 判断是否同步为7天数据
     * @param syncEndDate
     * @return
     * @throws Exception
     */
    public boolean isSyncTime(String syncEndDate) throws Exception {
        boolean bool = false;
        DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date dateOne = new Date();
        Date dateTwo = df.parse(syncEndDate);
        long diff = dateOne.getTime() - dateTwo.getTime();//这样得到的差值是毫秒级别
        long days = diff / (1000 * 60 * 60 * 24);
        if(days == 0) {
            bool = true;
        }
        return bool;
    }

}
